package de.ur.iw.sql;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.UUID;

import de.ur.iw.wiki.WikiPage;

public class WikiSQLHelper {

	private SQLHelper helper = new SQLHelper();

	public static final String DBNAME = "wikiwalks";

	private String url = "localhost", username = "root", password = "",
			tablename = "paths";

	public WikiSQLHelper(String url, String username, String password,
			String tablename) {
		this.url = url;
		this.username = username;
		this.password = password;
		this.tablename = tablename;
	}

	public void connect() throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {
		helper.connect(url, username, password);
		helper.createDataBase(DBNAME);
		createTable();
	}

	private void createTable() throws SQLException {
		String query = "CREATE TABLE IF NOT EXISTS "
				+ DBNAME
				+ "."
				+ tablename
				+ " (`name` varchar(100), `from` varchar(100), `to` varchar(100), `pathid` varchar(100), `position` INT)";
		helper.command(query);
	}

	public void insertPath(ArrayList<WikiPage> path) throws SQLException {
		
		String id = UUID.randomUUID().toString();
		
		int count = 0;
		
		if (path.size() == 1) {
			insert(null, path.get(0).getName(), null, id, 0);
			count++;
		} else if (path.size() > 1) {

			String current = path.get(0).getName();
			String to = path.get(1).getName();

			insert(null, current, to, id, 0);
			count++;

			for (int i = 1; i < path.size() - 1; i++) {
				String from = path.get(i - 1).getName();
				current = path.get(i).getName();
				to = path.get(i + 1).getName();
				insert(from, current, to, id, i);
				count++;
			}

			String from = path.get(path.size() - 2).getName();
			current = path.get(path.size() - 1).getName();
			insert(from, current, null, id, path.size() - 1);
			count++;
		}
		
		System.out.println("Path with a length of "+path.size()+" inserted");
	}

	private void insert(String from, String current, String to, String id, int position)
			throws SQLException {

		// remove all ' as it produces errors while inserting into sql
		if (from != null) {
			from = from.replaceAll("'", "");
		}
		if (to != null) {
			to = to.replaceAll("'", "");
		}

		String query = "INSERT INTO " + DBNAME + "." + tablename
				+ " (`name`, `from`, `to`, `pathid`, `position`) VALUES ('" + current
				+ "', '" + from + "', '" + to + "', '" + id + "', '" + position + "')";
		if (from == null && to == null) {
			query = "INSERT INTO " + DBNAME + "." + tablename
					+ " (`name`, `pathid`, `position`) VALUES ('" + current + "', '" + id
					+ "', '" + position + "')";
		} else if (from == null) {
			query = "INSERT INTO " + DBNAME + "." + tablename
					+ " (`name`, `to`, `pathid`, `position`) VALUES ('" + current + "', '"
					+ to + "', '" + id + "', '" + position + "')";
		} else if (to == null) {
			query = "INSERT INTO " + DBNAME + "." + tablename
					+ " (`name`, `from`, `pathid`, `position`) VALUES ('" + current
					+ "', '" + from + "', '" + id + "', '" + position + "')";
		}
		helper.command(query);
	}

}
